/**
 * model/sqlite/deviceBorrow.js
 */

const sqlite3 = require("sqlite3").verbose();
const config = require("../../common/config");
const util = require("../../common/util");

class BorrowDB {
  constructor() {
    this.dbFile = config.dbfile;
    this.instance = null;
    this.db = null;
  }

  static getInstance() {
    if (!this.instance) {
      this.instance = new BorrowDB();
    }
    return this.instance;
  }

  connect() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbFile, (err) => {
        if (err) reject(err);
        else resolve("connect ok.");
      });
    });
  }

  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve("close ok.");
      });
    });
  }

  /**
   * 查询单条借用记录，连带设备和用户信息
   * @param {number} id
   */
  async find(bookingId) {
    const sql = `
      SELECT br.*, d.title AS device_title, d.device_status, u.username 
      FROM borrow_records br
      JOIN lab_device d ON br.device_id = d.id
      JOIN users u ON br.user_id = u.id
      WHERE br.id = ?
    `;
    return new Promise((resolve, reject) => {
      this.db.get(sql, [bookingId], (err, row) => {
        if (err) reject(err);
        else resolve(row);
      });
    });
  }

  /**
   * 查询借用记录列表，支持分页，支持按status/userId/deviceId过滤，连带设备和用户信息
   * @param {object} params {limit, offset, filters}
   */
  findAll({ limit = -1, offset = -1, filters = {} } = {}) {
    let sql = `
      SELECT br.*, d.title AS device_title, d.device_status, u.username,
             CASE WHEN df.id IS NOT NULL THEN 1 ELSE 0 END as hasComment
      FROM borrow_records br
      JOIN lab_device d ON br.device_id = d.id
      JOIN users u ON br.user_id = u.id
      LEFT JOIN device_feedback df ON br.device_id = df.device_id AND br.user_id = df.user_id
      WHERE 1=1
    `;
    const params = [];

    if (filters.status) {
      sql += " AND br.status = ?";
      params.push(filters.status);
    }
    if (filters.userId) {
      sql += " AND br.user_id = ?";
      params.push(filters.userId);
    }
    if (filters.deviceId) {
      sql += " AND br.device_id = ?";
      params.push(filters.deviceId);
    }

    sql += " ORDER BY br.created_time DESC";

    if (limit > 0) {
      sql += " LIMIT ?";
      params.push(limit);
    }
    if (limit > 0 && offset >= 0) {
      sql += " OFFSET ?";
      params.push(offset);
    }

    console.log('SQL:', sql);
    console.log('Params:', params);

    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.error('Database error:', err);
          reject(err);
        } else {
          resolve(rows || []);
        }
      });
    });
  }

  /**
   * 检查设备在指定时间段是否可预约
   * @param {number} deviceId 
   * @param {string} startTime 
   * @param {string} endTime 
   */
  async checkDeviceAvailability(deviceId, startTime, endTime) {
    try {
      // 首先检查设备状态
      const device = await new Promise((resolve, reject) => {
        this.db.get(
          "SELECT device_status FROM lab_device WHERE id = ?",
          [deviceId],
          (err, row) => {
            if (err) {
              console.error('Error checking device status:', err);
              reject(err);
            } else {
              console.log('Device status check result:', row);
              resolve(row);
            }
          }
        );
      });

      if (!device) {
        console.log('Device not found');
        return false;
      }

      // 如果设备状态不是 available，直接返回 false
      if (device.device_status !== 'available') {
        console.log('Device is not available, current status:', device.device_status);
        return false;
      }

      // 检查时间段是否被预约
      const sql = `
        SELECT * FROM borrow_records 
        WHERE device_id = ? 
        AND status IN ('pending', 'approved')
        AND (
          -- 新预约的开始时间在已有预约的时间段内
          (start_time <= ? AND end_time > ?) OR
          -- 新预约的结束时间在已有预约的时间段内
          (start_time < ? AND end_time >= ?) OR
          -- 新预约完全包含已有预约
          (start_time <= ? AND end_time >= ?)
        )
      `;
      
      console.log('Checking time conflicts with params:', {
        deviceId,
        startTime,
        endTime,
        deviceStatus: device.device_status
      });

      const conflict = await new Promise((resolve, reject) => {
        this.db.get(sql, [
          deviceId,
          startTime, startTime,  // 新预约开始时间检查
          endTime, endTime,      // 新预约结束时间检查
          startTime, endTime     // 完全包含检查
        ], (err, row) => {
          if (err) {
            console.error('Error checking time conflicts:', err);
            reject(err);
          } else {
            if (row) {
              console.log('Found conflicting booking:', {
                existingStart: row.start_time,
                existingEnd: row.end_time,
                requestedStart: startTime,
                requestedEnd: endTime
              });
            }
            resolve(row);
          }
        });
      });

      const isAvailable = !conflict;
      console.log('Final availability check result:', {
        deviceStatus: device.device_status,
        hasTimeConflict: !!conflict,
        isAvailable
      });

      return isAvailable;
    } catch (err) {
      console.error('Error in checkDeviceAvailability:', err);
      throw err;
    }
  }

  /**
   * 创建预约记录
   * @param {object} borrowData 
   */
  async createBorrowRecord(borrowData) {
    const now = new Date().toISOString();
    const sql = `
      INSERT INTO borrow_records (
        user_id, device_id, borrow_time, start_time, end_time,
        purpose, status, created_time, updated_time
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    return new Promise((resolve, reject) => {
      this.db.run(sql, [
        borrowData.userId,
        borrowData.deviceId,
        borrowData.borrowTime,
        borrowData.startTime,
        borrowData.endTime,
        borrowData.purpose,
        borrowData.status,
        now,
        now
      ], function(err) {
        if (err) reject(err);
        else resolve(this.lastID);
      });
    });
  }

  /**
   * 更新预约状态
   * @param {number} deviceId 
   * @param {number} userId 
   * @param {string} status 
   * @param {string} remarks 
   */
  async updateBorrowStatus(deviceId, userId, status, remarks) {
    const now = new Date().toISOString();
    const db = this.db;
    
    return new Promise(async (resolve, reject) => {
      try {
        // 如果是批准预约，先检查时间冲突
        if (status === 'approved') {
          // 获取当前预约的详细信息
          const currentBooking = await new Promise((resolve, reject) => {
            db.get(
              `SELECT * FROM borrow_records 
               WHERE device_id = ? AND user_id = ? AND status = 'pending'`,
              [deviceId, userId],
              (err, row) => {
                if (err) reject(err);
                else resolve(row);
              }
            );
          });

          if (!currentBooking) {
            return reject(new Error('未找到待审核的预约记录'));
          }

          // 检查时间冲突
          const hasConflict = await new Promise((resolve, reject) => {
            const sql = `
              SELECT * FROM borrow_records 
              WHERE device_id = ? 
              AND status = 'approved'
              AND id != ?
              AND (
                -- 新预约的开始时间在已有预约的时间段内
                (start_time <= ? AND end_time > ?) OR
                -- 新预约的结束时间在已有预约的时间段内
                (start_time < ? AND end_time >= ?) OR
                -- 新预约完全包含已有预约
                (start_time <= ? AND end_time >= ?)
              )
            `;
            
            db.get(sql, [
              deviceId,
              currentBooking.id,
              currentBooking.start_time, currentBooking.start_time,
              currentBooking.end_time, currentBooking.end_time,
              currentBooking.start_time, currentBooking.end_time
            ], (err, row) => {
              if (err) reject(err);
              else resolve(!!row);
            });
          });

          if (hasConflict) {
            return reject(new Error('该时间段已被其他用户预约'));
          }
        }

        db.serialize(() => {
          db.run("BEGIN TRANSACTION");
          
          // 更新预约记录状态
          db.run(
            `UPDATE borrow_records 
             SET status = ?, remarks = ?, updated_time = ?
             WHERE device_id = ? AND user_id = ? AND status = 'pending'`,
            [status, remarks, now, deviceId, userId],
            function(err) {
              if (err) {
                console.error('Error updating borrow record:', err);
                db.run("ROLLBACK");
                return reject(err);
              }
              
              // 如果状态是已批准，更新设备状态为已借出
              if (status === 'approved') {
                console.log('Updating device status to borrowed for device:', deviceId);
                db.run(
                  `UPDATE lab_device 
                   SET device_status = 'borrowed', updated_time = ?
                   WHERE id = ?`,
                  [now, deviceId],
                  function(err2) {
                    if (err2) {
                      console.error('Error updating device status:', err2);
                      db.run("ROLLBACK");
                      return reject(err2);
                    }
                    console.log('Device status updated successfully');
                    db.run("COMMIT", (commitErr) => {
                      if (commitErr) {
                        console.error('Error committing transaction:', commitErr);
                        return reject(commitErr);
                      }
                      resolve(this.changes);
                    });
                  }
                );
              } else if (status === 'rejected') {
                // 如果状态是已拒绝，确保设备状态为可用
                console.log('Ensuring device is available after rejection for device:', deviceId);
                db.run(
                  `UPDATE lab_device 
                   SET device_status = 'available', updated_time = ?
                   WHERE id = ?`,
                  [now, deviceId],
                  function(err2) {
                    if (err2) {
                      console.error('Error updating device status:', err2);
                      db.run("ROLLBACK");
                      return reject(err2);
                    }
                    console.log('Device status updated to available after rejection');
                    db.run("COMMIT", (commitErr) => {
                      if (commitErr) {
                        console.error('Error committing transaction:', commitErr);
                        return reject(commitErr);
                      }
                      resolve(this.changes);
                    });
                  }
                );
              } else {
                db.run("COMMIT", (commitErr) => {
                  if (commitErr) return reject(commitErr);
                  resolve(this.changes);
                });
              }
            }
          );
        });
      } catch (err) {
        console.error('Error in updateBorrowStatus:', err);
        reject(err);
      }
    });
  }

  /**
   * 检查设备是否超期未归还
   * @param {number} deviceId 
   * @param {number} userId 
   */
  async checkOverdue(deviceId, userId) {
    const sql = `
      SELECT br.*, d.title AS device_title
      FROM borrow_records br
      JOIN lab_device d ON br.device_id = d.id
      WHERE br.device_id = ? 
      AND br.user_id = ? 
      AND br.status = 'approved'
      AND br.end_time < datetime('now')
    `;
    
    return new Promise((resolve, reject) => {
      this.db.get(sql, [deviceId, userId], (err, row) => {
        if (err) {
          console.error('检查超期失败:', err);
          reject(err);
        } else {
          resolve(row);
        }
      });
    });
  }

  /**
   * 获取所有超期未归还的设备
   */
  async getOverdueDevices() {
    const sql = `
      SELECT br.*, d.title AS device_title, u.username
      FROM borrow_records br
      JOIN lab_device d ON br.device_id = d.id
      JOIN users u ON br.user_id = u.id
      WHERE br.status = 'approved'
      AND br.end_time < datetime('now')
    `;
    
    return new Promise((resolve, reject) => {
      this.db.all(sql, [], (err, rows) => {
        if (err) {
          console.error('获取超期设备失败:', err);
          reject(err);
        } else {
          resolve(rows || []);
        }
      });
    });
  }

  /**
   * 归还设备
   * @param {number} bookingId 
   * @param {string} returnNote 归还备注
   */
  async returnBorrowRecord(bookingId, returnNote = '') {
    const now = new Date().toISOString();
  
    // 首先通过 bookingId 查询记录
    const borrow = await new Promise((resolve, reject) => {
      const sql = `
        SELECT br.*, d.device_status
        FROM borrow_records br
        JOIN lab_device d ON br.device_id = d.id
        WHERE br.id = ? 
        AND br.status = 'approved'
      `;
      
      console.log('Checking borrow record:', { bookingId });
      this.db.get(sql, [bookingId], (err, row) => {
        if (err) {
          console.error('Error checking borrow record:', err);
          reject(err);
        } else {
          console.log('Found borrow record:', row);
          resolve(row);
        }
      });
    });
  
    if (!borrow) {
      console.log('No active borrow record found');
      return 0;
    }
  
    return new Promise((resolve, reject) => {
      let borrowChanges = 0;
      let deviceChanges = 0;
      const outerThis = this;
    
      this.db.serialize(() => {
        this.db.run("BEGIN TRANSACTION");
        
        // 更新借用记录状态
        const updateBorrowSql = `
          UPDATE borrow_records 
          SET return_time = ?, 
              status = 'completed', 
              remarks = ?,
              updated_time = ? 
          WHERE id = ? 
          AND status = 'approved'
        `;
        
        console.log('Updating borrow record:', {
          returnTime: now,
          returnNote,
          bookingId
        });
        
        this.db.run(updateBorrowSql, 
          [now, returnNote, now, bookingId],
          function (err) {
            if (err) {
              console.error('Error updating borrow record:', err);
              outerThis.db.run("ROLLBACK");
              return reject(err);
            }
            borrowChanges = this.changes;
            console.log('Borrow record updated, changes:', borrowChanges);
    
            // 更新设备状态
            const updateDeviceSql = `
              UPDATE lab_device 
              SET device_status = 'available',
                  updated_time = ?
              WHERE id = ? 
              AND device_status = 'borrowed'
            `;
            
            console.log('Updating device status:', { deviceId: borrow.device_id });
            
            outerThis.db.run(updateDeviceSql, 
              [now, borrow.device_id],
              function (err2) {
                if (err2) {
                  console.error('Error updating device status:', err2);
                  outerThis.db.run("ROLLBACK");
                  return reject(err2);
                }
                deviceChanges = this.changes;
                console.log('Device status updated, changes:', deviceChanges);
                
                outerThis.db.run("COMMIT", (commitErr) => {
                  if (commitErr) {
                    console.error('Error committing transaction:', commitErr);
                    return reject(commitErr);
                  }
                  console.log('Transaction committed successfully');
                  resolve(borrowChanges + deviceChanges);
                });
              }
            );
          }
        );
      });
    });
  }

  /**
   * 取消预约
   * @param {number} bookingId 预约记录ID
   * @param {string} cancelReason 取消原因
   */
  async cancelBooking(bookingId, cancelReason = '') {
    const now = new Date().toISOString();
    const db = this.db;
    
    return new Promise((resolve, reject) => {
      db.serialize(() => {
        db.run("BEGIN TRANSACTION");
        
        // 检查预约记录是否存在且状态为pending
        db.get(
          `SELECT * FROM borrow_records 
           WHERE id = ? 
           AND status = 'pending'`,
          [bookingId],
          (err, row) => {
            if (err) {
              console.error('Error checking booking record:', err);
              db.run("ROLLBACK");
              return reject(err);
            }
            
            if (!row) {
              db.run("ROLLBACK");
              return reject(new Error('未找到可取消的预约记录或预约已开始'));
            }
            
            // 更新预约状态为已取消
            db.run(
              `UPDATE borrow_records 
               SET status = 'cancelled', 
                   remarks = ?,
                   updated_time = ? 
               WHERE id = ? 
               AND status = 'pending'`,
              [cancelReason, now, bookingId],
              function(err2) {
                if (err2) {
                  console.error('Error cancelling booking:', err2);
                  db.run("ROLLBACK");
                  return reject(err2);
                }
                
                console.log('Booking cancelled successfully');
                db.run("COMMIT", (commitErr) => {
                  if (commitErr) {
                    console.error('Error committing transaction:', commitErr);
                    return reject(commitErr);
                  }
                  resolve(this.changes);
                });
              }
            );
          }
        );
      });
    });
  }

  /**
   * 添加设备使用记录
   * @param {number} deviceId 
   * @param {number} userId 
   * @param {string} usageNote 使用记录
   */
  async addUsageRecord(deviceId, userId, usageNote) {
    const now = new Date().toISOString();
    const sql = `
      INSERT INTO device_usage_records (
        device_id, user_id, usage_note, created_time
      ) VALUES (?, ?, ?, ?)
    `;

    return new Promise((resolve, reject) => {
      this.db.run(sql, [deviceId, userId, usageNote, now], function(err) {
        if (err) {
          console.error('Error adding usage record:', err);
          reject(err);
        } else {
          resolve(this.lastID);
        }
      });
    });
  }

  /**
   * 获取设备使用记录
   * @param {number} deviceId 
   * @param {number} limit 
   * @param {number} offset 
   */
  async getUsageRecords(deviceId, limit = -1, offset = -1) {
    const sql = `
      SELECT ur.*, u.username
      FROM device_usage_records ur
      JOIN users u ON ur.user_id = u.id
      WHERE ur.device_id = ?
      ORDER BY ur.created_time DESC
      ${limit > 0 ? 'LIMIT ?' : ''}
      ${limit > 0 && offset >= 0 ? 'OFFSET ?' : ''}
    `;

    const params = [deviceId];
    if (limit > 0) {
      params.push(limit);
      if (offset >= 0) {
        params.push(offset);
      }
    }

    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.error('Error getting usage records:', err);
          reject(err);
        } else {
          resolve(rows || []);
        }
      });
    });
  }
}
module.exports = BorrowDB;


